/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Dblayer;
import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author bruhn
 */
public class DBVare implements IFDBVare {

    private Connection con;
    public DBVare()
    {
	con = DBConnection.getInstance().getDBcon();
    }
    
    public Vare findVare(int vareID, boolean retriveAssociation) {
        String wClause = "  VareID = '" + vareID + "'";
        return singleWhere(wClause, retriveAssociation);
    }

    
    public int opretVare(Vare vare) {
    int rc = -1;
    String query="INSERT INTO semVare(vareID, vareNavn, beskrivelse, pris)  VALUES('"+
	  		vare.getVareID()  + "','"  +
			vare.getVareNavn() + "','" +
			vare.getBeskrivelse() + "','" +
			vare.getPris() + "')";

      System.out.println("insert : " + query);
      try{ // insert new customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in semVare db: "+ex);
	   }
	   return(rc);
	}

	public void slet(int vareID)
	{
               int rc=-1;

	  	String query="DELETE FROM semVare WHERE vareID = '" +
				vareID + "'";
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in semVare db: "+ex);
   	        }
    }

    public int redigerVare(Vare vare) {
       Vare vareObj  = vare;
		int rc=-1;

		String query="UPDATE semVare SET "+
                        "vareID ='" + vareObj.getVareID() + "', " +
		    	"vareNavn ='" + vareObj.getVareNavn() + "', " +
                        "beskrivelse ='" + vareObj.getBeskrivelse() + "', " +
                        "pris ='" + vareObj.getPris() + "' " +
		        " WHERE vareID = '"+ vareObj.getVareID() + "'";

                System.out.println("Update query:" + query);
  		try{ // update vare
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in vare db: "+ex);
	  	}
		return(rc);
    }

    public void sletVare(int vareID) {
        int rc=-1;

	  	String query="DELETE FROM semVare WHERE vareID = '" +
				vareID + "'";
                System.out.println(query);
	  	try{ // delete from bestilling
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in semVare db: "+ex);
   	        }
    }

    private ArrayList<Vare> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Vare> list = new ArrayList<Vare>();

	    String query =  buildQuery(wClause);

            try{ // read the persiCustomer from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		int snr=0;
		while( results.next() ){
	     	 Vare vareObj = new Vare();
		 vareObj = buildVare(results);
                 list.add(vareObj);
		}//end while
                 stmt.close();
//                 if(retrieveAssociation)
//                 {   //The city is to be build as well
//                     for(Vare vareObj : list){
//			IFDBMedarbejder dbMedarbejderObj = new DBMedarbejder();
//                        Medarbejder medarbejderObj = dbMedarbejderObj.findMedarbejder(brugerObj.getMedNr());
//                        System.out.println("medarbejder is seleceted ");
//			brugerObj.setMedNr(medarbejderObj);
//                     }
//                 }//end if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

    //Singelwhere is used when we only select one vare
    private Vare singleWhere(String wClause, boolean retrieveAssociation)
    {
           ResultSet results;
           Vare vareObj = new Vare();

	   String query =  buildQuery(wClause);
           System.out.println(query);
		try{ // read the persiVare from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            vareObj = buildVare(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {
//                                IFDBVare dbVareObj = new DBVare();
//                                Vare vareObj = dbVareObj.findVare(vareObj.getVareID());
//                                System.out.println("vare is seleceted ");
//                                vareObj.setVareID(vareObj);
//                            }
			}
                        else{ //no vare was found
                            vareObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return vareObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT vareID, vareNavn, pris, beskrivelse FROM semVare";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Vare buildVare(ResultSet results)
        {   
            Vare vareObj = new Vare();
            try{ // the columns from the table persiCustomer are used
                vareObj.setVareID(results.getInt("vareID"));
                vareObj.setVareNavn(results.getString("vareNavn"));
                vareObj.setPris(results.getDouble("pris"));
		vareObj.setBeskrivelse((results.getString("beskrivelse")));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Vare object");
         }
         return vareObj;
      }

    public ArrayList<Vare> hentVare(boolean retriveAssociation) {
        return miscWhere("", retriveAssociation);
    }
}
